﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using DiscoveryLogic.Analytics.DataObjects;
using DiscoveryLogic.Common.Utilities;

namespace DiscoveryLogic.Analytics.BusinessLogic.Importers
{
    public class PubImportUtil
    {
        public static List<PublicationDO> GetPublicationsByPMID(List<int> pmids)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Medline"].ConnectionString;
            List<PublicationDO> pubs=new List<PublicationDO>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = @"select 
a.PMID,
a.Affiliation,
a.AuthorList,
c.Title as JournalName,
a.ArticleDate,
a.ArticleTitle
from dbo.Citation a 
left join dbo.JournalIssue b on a.JournalIssueID=b.JournalIssueID 
left join dbo.Journal c on c.JournalID=b.JournalID where a.PMID={0}";
                foreach (int pmid in pmids)
                {
                    cmd.CommandText = string.Format(sql, pmid);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if(reader.Read())
                    {
                        PublicationDO pub = new PublicationDO();
                        pub.PMID = int.Parse(reader["PMID"].ToString());
                        pub.Affiliation = reader["Affiliation"] == DBNull.Value ? "" : reader["Affiliation"].ToString();
                        pub.Authors = reader["AuthorList"] == DBNull.Value ? "" : reader["AuthorList"].ToString();
                        pub.Title = reader["ArticleTitle"] == DBNull.Value ? "" : reader["ArticleTitle"].ToString();
                        pub.PubDate = reader["ArticleDate"] == DBNull.Value
                                          ? DateTime.MinValue
                                          : DateTime.Parse(reader["ArticleDate"].ToString());
                        pub.Journal = reader["JournalName"] == DBNull.Value ? "" : reader["JournalName"].ToString();
                        pubs.Add(pub);
                    }
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return pubs;
        }

        public static List<PublicationDO> GetPublicationsByWosItemIDs(List<int> wosItemIDs)
        {
            List<PublicationDO> pubs=new List<PublicationDO>();
            string connStr = ConfigurationManager.ConnectionStrings["WebOfScience"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql1 = @"select distinct 
	a.SourceItemId,
	a.ArticleTitle,
	a.Abstract,
	b.PublicationDate as PubDate,
	b.PublicationYear as PubYear,
	c.Title as JournalName
from 
SourceItem a, 
SourceIssue b, 
Journal c
where 
	a.SourceIssueID=b.SourceIssueID and 
	(b.ISSN=c.ISSN) and 
	a.SourceItemID={0}";

                string sql2 =
                    @"select 
b.SourceItemID,
a.AuthorID,
a.AuthorName,
a.LastName,
a.FirstName,
a.NameSuffix,
a.Email 
from 
Author a,
SourceItemAuthor b
where 
a.AuthorID=b.AuthorID 
and b.SourceItemID={0}";

                string sql3 = @"select 
b.SourceItemID,
a.AddressID,
a.FullAddress,
a.Organization,
isnull(a.SubOrganization1,'') + ' '+isnull(a.SubOrganization2,'')+' '+isnull(a.SubOrganization3,'') as Department,
a.StreetAddress,
a.City,
a.ProvinceState,
a.Country,
a.PostalCode 
from 
[Address] a,
SourceItemResearchAddress b 
where 
a.AddressID=b.AddressID 
and b.SourceItemID={0}";

                foreach (int itemID in wosItemIDs)
                {
                    cmd.CommandText = string.Format(sql1, itemID);
                    SqlDataReader reader = cmd.ExecuteReader();
                    PublicationDO pub = new PublicationDO();
                    if (reader.Read())
                    {
                        pub.PMID = int.Parse(reader["SourceItemId"].ToString());
                        pub.Title = reader["ArticleTitle"] == DBNull.Value ? "" : reader["ArticleTitle"].ToString();
                        string pubDate = reader["PubDate"] == DBNull.Value
                                          ? ""
                                          : reader["PubDate"].ToString();
                        int pubYear = reader["PubYear"] == DBNull.Value
                                          ? 0
                                          : int.Parse(reader["PubYear"].ToString());
                        if(!string.IsNullOrEmpty(pubDate) && pubYear>0)
                        {
                            pub.PubDate = GetPubDate(pubDate, pubYear);
                        }
                        pub.Journal = reader["JournalName"] == DBNull.Value ? "" : reader["JournalName"].ToString();
                    }
                    reader.Close();

                    if(pub.PMID>0)
                    {
                        cmd.CommandText = string.Format(sql2, itemID);
                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            string authorName = reader["AuthorName"] == DBNull.Value
                                                    ? ""
                                                    : reader["AuthorName"].ToString();
                            if(string.IsNullOrEmpty(pub.Authors))
                                pub.Authors = authorName;
                            else
                                pub.Authors += ", " + authorName;
                        }
                        reader.Close();

                        cmd.CommandText = string.Format(sql3, itemID);
                        reader = cmd.ExecuteReader();
                        if(reader.Read())
                        {
                            pub.Affiliation = reader["FullAddress"] == DBNull.Value
                                                 ? ""
                                                 : reader["FullAddress"].ToString();
                        }
                        reader.Close();

                        pubs.Add(pub);
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return pubs;
        }

        private static DateTime GetPubDate(string pubDate, int year)
        {
            int month = 0;
            string[] months = {"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"};
            string[] seasons = {"SPR", "SUM", "FAL", "WIN"};
            if(pubDate.Length>=3)
            {
                string mon = pubDate.Substring(0, 3);
                mon = mon.ToUpper();
                for(int i=0;i<months.Length;i++)
                {
                    if (mon == months[i])
                    {
                        month = i + 1;
                        break;
                    }
                }
                if(month<=0)
                {
                    for(int i=0;i<seasons.Length;i++)
                    {
                        if(mon==seasons[i])
                        {
                            month = 3 + i*3;
                        }
                    }
                }
            }
            if (month <= 0)
                month = 1;
            return new DateTime(year, month, 1);
        }

        public static List<PublicationDO> GetPublicationsByMeshTerms(List<string> meshTerms)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Medline"].ConnectionString;
            Dictionary<int, PublicationDO> pubs = new Dictionary<int, PublicationDO>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = @"select 
a.PMID,
a.Affiliation,
a.AuthorList,
c.Title as JournalName,
a.ArticleDate,
a.ArticleTitle
from dbo.Citation a 
left join dbo.JournalIssue b on a.JournalIssueID=b.JournalIssueID 
left join dbo.Journal c on c.JournalID=b.JournalID where a.PMID in (
select DISTINCT
		C.PMID 
FROM
	Citation C
	JOIN CitationMesh CM
		ON C.PMID = CM.PMID		
	JOIN MeshDescriptor MD
		ON CM.MeshDescriptorId = MD.MeshDescriptorId
		AND CM.MajorTopicYN = 1
		AND MD.MeshDescriptor = '{0}'
	JOIN CitationSourceItem CSI
		ON C.PMID = CSI.PMID
		AND YEAR(DLPubDate) = 2008
	LEFT OUTER JOIN SynapseNew..Link L
			ON L.FromObjectId = C.PMID
				AND L.ToObjectTypeId = 1
				AND L.FromObjectTypeId = 2
				AND L.LinkSourceId = 1
		LEFT OUTER JOIN NIHProject..Project P
			ON P.ProjectId = L.ToObjectId
		LEFT OUTER JOIN NIHProject..Appl A
			ON P.CurrentApplId = A.APPL_ID
		LEFT OUTER JOIN impacii_mirror.irdb.external_orgs_mv O
			ON O.external_org_id = A.external_org_id
	WHERE
		O.Org_Name IS NOT NULL)";
                foreach (string meshterm in meshTerms)
                {
                    cmd.CommandText = string.Format(sql, meshterm);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        PublicationDO pub = new PublicationDO();
                        pub.PMID = int.Parse(reader["PMID"].ToString());
                        pub.Affiliation = reader["Affiliation"] == DBNull.Value ? "" : reader["Affiliation"].ToString();
                        pub.Authors = reader["AuthorList"] == DBNull.Value ? "" : reader["AuthorList"].ToString();
                        pub.Title = reader["ArticleTitle"] == DBNull.Value ? "" : reader["ArticleTitle"].ToString();
                        pub.PubDate = reader["ArticleDate"] == DBNull.Value
                                          ? DateTime.MinValue
                                          : DateTime.Parse(reader["ArticleDate"].ToString());
                        pub.Journal = reader["JournalName"] == DBNull.Value ? "" : reader["JournalName"].ToString();
                        if (!pubs.ContainsKey(pub.PMID))
                            pubs.Add(pub.PMID, pub);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return pubs.Values.ToList();
        }
    }
}
